<?php
class Member extends AppModel {
	var $name = 'Member';
    var $useTable = 'members';
    var $displayField = 'name';
    var $validate = array(
        'email' => array(
            'isEmail' => array(
                'rule'     => 'email',
                'required' => true,
                'message'  => 'Please supply a valid email address'
            )
        )
    );
    var $ids = array();


    public function accountList ($params) {
        $page = $params['page'];
        $cond = ($params['qtype'] == 'fullname2') ? 'm.`status`="R" ' : 'm.`status`!="R" ';

        if ($params['query'] && $params['qtype']) {
            $qtype = ($params['qtype'] == 'fullname2') ? 'fullname' : $params['qtype'];
            $cond .= 'AND m.`'.$qtype.'` LIKE "%'.$params['query'].'%" ';
        }

        $sql = 'SELECT COUNT(*) AS total FROM `members` AS m ' .
               'WHERE '.$cond;
        $ttl = $this->query($sql);

        $total = (count($ttl) == 0) ? 0 : $ttl[0][0]['total'];
        if ($total == 0) {
            return 0;
        }

        $rpp   = isset($params['rp']) ? $params['rp'] : 10;
        $start = (($page-1) * $rpp);
        $sort  = empty($params['sortname']) ? 'id' : $params['sortname'];
        $ord   = (empty($params['sortorder']) || $params['sortorder'] == 'asc') ? 'ASC' : 'DESC';
        $sql   = 'SELECT m.* FROM `members` AS m ' .
                 'WHERE '.$cond .
                 'ORDER BY m.`'.$sort.'` '.$ord.' ' .
                 'LIMIT '.$start.', '.$rpp;
        $data = $this->query($sql);
        $rec  = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $rs) {
            if ($rs['m']['status'] == 'D') {
                $status = 'Distributor';
            } else {
                $status = 'Unknown';
            }


            $id = $rs['m']['id'];

            $actions   = '<div class="editRecord" onclick="editRecord('.$id.');"></div>';
            $loginDate = ($rs['m']['last_login_time']) ? $this->serverToClientTime($rs['m']['last_login_time'], 'd/m/Y H:i a') : '-';
            $joinDate  = ($rs['m']['join_date']) ? $this->serverToClientTime($rs['m']['join_date'], 'd/m/Y H:i a') : '-';
            $renewDate = ($rs['m']['renew_date']) ? $this->serverToClientTime($rs['m']['renew_date'], 'd/m/Y H:i a') : '-';
            $dob       = ($rs['m']['dob']) ? $this->serverToClientTime($rs['m']['dob'], 'd/m/Y') : '-';
            $transfer  = ($rs['m']['transfer_bank'] == 'Y') ? 'Yes' : 'No';
            $created   = $this->serverToClientTime($rs['m']['created'], 'd/m/Y H:i a');
            $modified  = $this->serverToClientTime($rs['m']['modified'], 'd/m/Y H:i a');

            $cell = array(
                $rs['m']['id'], $rs['m']['international_id'], $rs['m']['company_name'], $rs['m']['company_reg_no'], $rs['m']['fullname'],
                $dob, $rs['m']['identity'], $rs['m']['residential_address'], $rs['m']['residential_postcode'], $rs['m']['postal_address'], $rs['m']['postal_postcode'],
                $rs['m']['country'], $rs['m']['email'], $rs['m']['password'], $rs['m']['telephone'], $rs['m']['mobile'], $rs['m']['bv'], $rs['m']['refund_times'],
                $status, $transfer, $rs['m']['bank'], $rs['m']['branch'], $rs['m']['bank_account'], $rs['m']['receiver_name'], $rs['m']['receiver_telephone'],
                $rs['m']['tree_level'], $rs['m']['login_times'], $loginDate, $joinDate, $renewDate, $created, $modified, $actions
            );

            $rec[] = array(
                'id'   => $rs['m']['id'],
                'cell' => $cell
            );
        }

        return array(
            'page'  => $page,
            'total' => $total,
            'rows'  => $rec
        );
    } // accountList

    public function updateBv ($uid, $bv) {
        $this->query('UPDATE `members` SET `bv`=`bv`+'.$bv.' WHERE `id`='.$uid);

        if ($this->getAffectedRows() == 0) {
            return false;
        }

        $this->id = $uid;

        return $this->field('bv');
    } // updateBv

    public function addRefundTimes ($uid) {
        $this->query('UPDATE `members` SET `refund_times`=`refund_times`+1 WHERE `id`='.$uid);

        if ($this->getAffectedRows() == 0) {
            return false;
        }

        return true;
    } // addRefundTimes

    public function topSponsor ($filters) {
        $cond = $this->_generateCondition($filters);
        $sql  = 'SELECT m.`sponsor_id`, m.`rank_id`, m.`placement`, s.`rank_id`, s.`fullname`, s.`country` FROM `members` AS m ' .
                'INNER JOIN `members` as s ' .
                'ON m.`sponsor_id`=s.`id` ' .
                'WHERE m.`status`="D"'.$cond.' ' .
                'ORDER BY m.`sponsor_id` ASC';
        $data = $this->query($sql);
        $resp = array();
        $curr = '';
        $i    = -1;

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $r) {
            if ($curr != $r['m']['sponsor_id']) {
                $curr = $r['m']['sponsor_id'];
                $i++;

                $productName = $this->_getProductName($r['i']['product_id']);
                $resp['list'][$i] = array(
                    'id'      => $r['m']['sponsor_id'],
                    'name'    => $r['s']['fullname'],
                    'country' => $r['s']['country'],
                    'metal'   => $r['s']['rank_id'],
                    'total'   => 0,
                    'gold'    => 0,
                    'silver'  => 0,
                    'brown'   => 0
                );
            }

            $rankId    = $r['m']['rank_id'];
            $placement = $r['m']['placement'];

            $resp['list'][$i]['total'] += 1;
        }

        return $resp;
    } // topSponsor

    public function findDownlines ($id) {
        $cond = ($id) ? '`PlacementID`='.$id : '`PlacementID`=1';
        $sql  = 'SELECT `UserID`, `FullName`, `EMail`, `PlacementFlag` FROM `users` AS u WHERE '.$cond;
        $data = $this->query($sql);
        $resp = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $u) {
            $p        = $u['u']['PlacementFlag'];
            $resp[$p] = array(
                'id'    => $u['u']['UserID'],
                'name'  => $u['u']['FullName'],
                'email' => $u['u']['EMail']
            );
        }

        return $resp;
    } // findDownlines

    public function totalDownlines ($id, $leg) {
        $sql = 'SELECT COUNT(*) AS total FROM `downlines` AS d ' .
               'WHERE d.`root_id`='.$id.' AND d.`leg`="'.$leg.'"';
        $ttl = $this->query($sql);

        return (count($ttl) == 0) ? 0 : $ttl[0][0]['total'];
    } // totalDownlines

    public function indented ($id, $level=1) {
        if ($level == 1) {
            $this->_initDownlines($id);
        }

        $lvl  = $level * 12;
        $sql  = 'SELECT * FROM `downlines` AS d WHERE d.`root_id`='.$id.' AND d.`level`<'.$lvl.' ORDER BY d.`id` ASC';
        $data = $this->query($sql);
        $resp = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $s) {
            $l = $s['d']['leg'];

            if ($s['d']['status'] == 'G') {
                $medal = __('Gold');
            } else if ($s['d']['status'] == 'S') {
                $medal = __('Silver');
            } else if ($s['d']['status'] == 'B') {
                $medal = __('Brown');
            } else {
                $medal = __('-----', true);
            }

            $name = $s['d']['name'];
            for ($x=1; $x<$s['d']['level']; $x++) {
                $name = '.'.$name;
            }

            $member_id  = ($s['d']['international_id']) ? $s['d']['international_id'] : '('.$s['d']['member_id'].')';
            $resp[] = array(
                'mem_id' => $member_id,
                'name'   => $name,
                'phone'  => $s['d']['telephone'],
                'level'  => $s['d']['level'].$s['d']['placement'],
                'medal'  => $medal
            );
        }

        return $resp;
    } // indented

    public function downlines ($params, $id) {
        $page = $params['page'];
        $cond = 'd.`root_id`='.$id.' ';

        if ($page == 1) {
            $this->_initDownlines($id);
        }

        if ($params['query'] && $params['qtype']) {
            $cond .= 'AND d.`'.$params['qtype'].'` LIKE "%'.$params['query'].'%" ';
        }

        $sql = 'SELECT COUNT(*) AS total FROM `downlines` AS d ' .
               'WHERE '.$cond;
        $ttl = $this->query($sql);

        $total = (count($ttl) == 0) ? 0 : $ttl[0][0]['total'];
        if ($total == 0) {
            return 0;
        }

        $rpp   = isset($params['rp']) ? $params['rp'] : 10;
        $start = (($page-1) * $rpp);
        $ord   = (empty($params['sortorder']) || $params['sortorder'] == 'asc') ? 'ASC' : 'DESC';
        $sort  = ($params['sortname'] == 'level') ? 'd.`level` '.$ord.', d.`placement`' : 'd.`'.$params['sortname'].'`';
        $sql   = 'SELECT d.* FROM `downlines` AS d ' .
                 'WHERE '.$cond .
                 'ORDER BY '.$sort.' '.$ord.' ' .
                 'LIMIT '.$start.', '.$rpp;
        $data = $this->query($sql);
        $rec  = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $rs) {
            $acts = '<div title="'.__('Details',true).'" class="tree" style="margin-left:5px;"></div><div class="clear" style="height:0; padding:0;"></div>';
            $cell = array($rs['d']['international_id'], $rs['d']['country'], $rs['d']['name'], $rs['d']['telephone'], $rs['d']['level'].$rs['d']['placement'], $acts);

            $rec[] = array(
                'id'   => $rs['d']['id'],
                'cell' => $cell
            );
        }

        return array(
            'page'  => $page,
            'total' => $total,
            'rows'  => $rec
        );
    } // downlines

    public function sponsored ($id) {
        $this->_initDownlines($id);

        $sql  = 'SELECT * FROM `downlines` AS d WHERE d.`root_id`='.$id.' AND d.`sponsor_id`='.$id.' ORDER BY d.`level` ASC, d.`placement` ASC';
        $data = $this->query($sql);
        $resp = array();

        if (count($data) == 0) {
            return 0;
        }

        foreach ($data as $s) {
            $l = $s['d']['leg'];

            if ($s['d']['status'] == 'G') {
                $medal = __('Gold', true);
            } else if ($s['d']['status'] == 'S') {
                $medal = __('Silver', true);
            } else if ($s['d']['status'] == 'B') {
                $medal = __('Brown', true);
            } else if ($s['d']['status'] == 'D') {
                $medal = __('Distributor', true);
            } else {
                $medal = __('Member', true);
            }

            $resp[$l][] = array(
                'id'      => $s['d']['international_id'],
                'mem_id'  => $s['d']['member_id'],
                'country' => $s['d']['country'],
                'name'    => $s['d']['name'],
                'level'   => $s['d']['level'].$s['d']['placement'],
                'medal'   => $medal
            );
        }

        return $resp;
    } // sponsored

    public function nodeInfo ($node_id) {
        $sql  = 'SELECT `id`, `sponsor_id`, `international_id`, `join_date`, `fullname`, `country`, `mobile` FROM `members` AS m WHERE `id`='.$node_id;
        $data = $this->query($sql);
        $resp = array();

        if (count($data) == 0) {
            return 0;
        }

        if ($data[0]['m']['sponsor_id']) {
            $sponsor = $this->_findSponsor($data[0]['m']['sponsor_id']);
        } else {
            $sponsor = '-';
        }

        $this->ids = array($node_id);
        $sponsored = $this->_totalSponsored($node_id);

        $resp = array(
            'id'   => 'node'.$node_id,
            'name' => ($data[0]['m']['fullname']) ? $data[0]['m']['fullname'] : $node_id,
            'data' => array(
                'i_id'     => ($data[0]['m']['international_id']) ? $data[0]['m']['international_id'] : '',
                'country'  => ($data[0]['m']['country']) ? $data[0]['m']['country'] : '-',
                'mobile'   => ($data[0]['m']['mobile']) ? $data[0]['m']['mobile'] : '-',
                'joinDate' => ($data[0]['m']['join_date']) ? $this->serverToClientTime($data[0]['m']['join_date'],'d/m/Y H:i a') : '-',
                'sponsor'  => $sponsor,
                'left'     => $sponsored['left'],
                'right'    => $sponsored['right']
            )
        );

        return $resp;
    } // nodeInfo

    public function findGenealogyNode ($node_id, $level=2) {
        $sql  = 'SELECT `id`, `sponsor_id`, `international_id`, `join_date`, `fullname`, `country`, `mobile` ' .
                'FROM `members` AS m ' .
                'WHERE `member_id`='.$node_id.' ORDER BY `placement` ASC';
        $data = $this->query($sql);
        $resp = array();

        if (count($data) == 0) {
            return 0;
        }
        $level -= 1;

        foreach ($data as $m) {
            $id   = $m['m']['id'];
            $name = ($m['m']['fullname']) ? $m['m']['fullname'] : $id;
            $i_id = ($m['m']['international_id']) ? $m['m']['international_id'] : '';
            $join = ($m['m']['join_date']) ? $this->serverToClientTime($m['m']['join_date'],'d/m/Y H:i a') : '-';
            $data = $children = array();

            $this->ids[] = $id;

            if ($level > 0) {
                $sql = 'SELECT COUNT(*) FROM `members` WHERE `member_id`='.$id;
                $cnt = $this->query($sql);
                if ($cnt[0][0] > 0) {
                    $children = $this->findGenealogyNode($id, $level);
                }
            }

            if ($m['m']['sponsor_id']) {
                $sponsor = $this->_findSponsor($m['m']['sponsor_id']);
            } else {
                $sponsor = '-';
            }

            //$sponsored = $this->_totalSponsored($id);

            $data   = array(
                'i_id'     => $i_id,
                'country'  => ($m['m']['country'])?$m['m']['country']:'-',
                'mobile'   => ($m['m']['mobile'])?$m['m']['mobile']:'-',
                'joinDate' => $join,
                'sponsor'  => $sponsor/*,
                'left'     => $sponsored['left'],
                'right'    => $sponsored['right']*/
            );
            $resp[] = array(
                'id'       => 'node'.$id,
                'name'     => $name,
                'data'     => $data,
                'children' => $children
            );
        }

        return $resp;
    } // findGenealogyNode

    public function afterSave ($created) {
        if (!$created) { // it's only an update record
            return;
        }

        $upline_id = (int) $this->id;
        $inter_id  = $this->field('international_id');

        if (!$inter_id) {
            return;
        }

        $dt  = date('Y-m-d H:i:s');
        $id1 = $inter_id + 1;
        $id2 = $inter_id + 2;
        $sql = 'INSERT INTO members (`member_id`, `international_id`, `fullname`, `placement`, `created`, `modified`) VALUES '.
               '('.$upline_id.', "'.$id1.'", "Reserved", "A", "'.$dt.'", "'.$dt.'"), '.
               '('.$upline_id.', "'.$id2.'", "Reserved", "B", "'.$dt.'", "'.$dt.'")';
        $this->query($sql);
    } // afterSave


    private function _generateCondition ($filters) {
        $cond = '';

        if (!empty($filters['date']) && strpos($filters['date'], ' - ')) {
            list($start, $end) = split(' - ', $filters['date']);

            list($day, $month, $year) = split('[/.-]', $start);
            $start = $year.'-'.$month.'-'.$day.' 00:00:00';
            list($day, $month, $year) = split('[/.-]', $end);
            $end = $year.'-'.$month.'-'.$day.' 23:59:59';

            $cond = ' AND m.`join_date` > "'.$start.'" AND m.`join_date` < "'.$end.'"';
        } else if (!empty($filters['date'])) {
            list($day, $month, $year) = split('[/.-]', $filters['date']);
            $cond = ' AND m.`join_date` LIKE "'.$year.'-'.$month.'-'.$day.'%"';
        }
        if (!empty($filters['stockist'])) {
            $cond .= ' AND o.`stockist_id`='.$filters['stockist'];
        }

        return $cond;
    } // _generateCondition

    private function _findSponsor ($sid) {
        $sql = 'SELECT `international_id`, `fullname` FROM `members` as s WHERE s.`id`='.$sid;
        $spr = $this->query($sql);

        $mem_id  = isset($spr[0]['s']['international_id']) ? $spr[0]['s']['international_id'] : '';
        $sponsor = isset($spr[0]['s']['fullname']) ? $spr[0]['s']['fullname'] : '';

        if ($sponsor && $mem_id) {
            return $mem_id.'/'.$sponsor;
        } else if ($sponsor) {
            return $sponsor;
        } else if ($mem_id) {
            return $mem_id;
        } else {
            return '-';
        }
    } // _findSponsor

    private function _totalSponsored ($uid) {
        $left = $this->find('count', array(
            'conditions' => 'sponsor_id='.$uid.' AND placement="A"'
        ));
        $right = $this->find('count', array(
            'conditions' => 'sponsor_id='.$uid.' AND placement="B"'
        ));

        return array(
            'left'  => $left,
            'right' => $right
        );
    } // _totalSponsored

    private function _initDownlines ($id) {
        $data = $this->query('SELECT `time` FROM `downlines` AS d WHERE `root_id`='.$id.' LIMIT 1');
        $time = time();

        if (empty($data[0]['d']['time']) || $time - $data[0]['d']['time'] > 604800) { // re-generate downlines tree after 1 week
            set_time_limit(0);
            ignore_user_abort(true);
            $this->query('DELETE FROM `downlines` WHERE `root_id`='.$id);
            $this->_generateDownlines($id);
        }

        return true;
    } // _initDownlines

    private function _generateDownlines ($root_id, $node_id=0, $level=1, $leg='') {
        $node_id = empty($node_id) ? $root_id : $node_id;

        $sql  = 'SELECT `id`, `member_id`, `sponsor_id`, `international_id`, `fullname`, `country`, `telephone`, `status`, `medal`, `placement` ' .
                'FROM `members` AS m WHERE `member_id`='.$node_id.' ORDER BY `placement` ASC, `fullname` ASC';
        $data = $this->query($sql);

        if (count($data) == 0) {
            return 0;
        }

        $next_level = $level + 1;

        foreach ($data as $m) {
            $iid  = ($m['m']['international_id']) ? $m['m']['international_id'] : $m['m']['id'];
            $name = ($m['m']['fullname'] == 'Reserved') ? '('.$iid.')' : $m['m']['fullname'];
            $ctry = ($m['m']['country']) ? $m['m']['country'] : '-';
            $tel  = ($m['m']['telephone']) ? $m['m']['telephone'] : '-';
            $lvl  = $level;
            $legs = empty($leg) ? $m['m']['placement'] : $leg;

            if ($m['m']['medal']) {
                if ($m['m']['medal'] == 1) {
                    $status = 'G';
                } else if ($m['m']['medal'] == 2) {
                    $status = 'S';
                } else {
                    $status = 'B';
                }
            } else {
                $status = $m['m']['status'];
            }

            $sql = 'INSERT INTO `downlines` (`root_id`, `member_id`, `sponsor_id`, `international_id`, `name`, `country`, `telephone`, `status`, `level`, `placement`, `leg`, `time`) ' .
                   'VALUES ('.$root_id.', '.$m['m']['id'].', '.$m['m']['sponsor_id'].', '.$iid.', "'.addslashes($name).'", "'.addslashes($ctry).'", "'.addslashes($tel).'", "' .
                   $status.'", '.$lvl.', "'.$m['m']['placement'].'", "'.$legs.'", '.time().')';
            $this->query($sql);

            $sql = 'SELECT COUNT(*) FROM `members` WHERE `member_id`='.$m['m']['id'];
            $cnt = $this->query($sql);
            if ($cnt[0][0] > 0) {
                $this->_generateDownlines($root_id, $m['m']['id'], $next_level, $leg);
            }
        }

        return true;
    } // _generateDownlines

    private function _generateDownlines2 ($root_id) {
        $this->loadModel('Membership');

        $data = $this->Membership->getMemberTree($root_id);
    } // _generateDownlines2
} // Member
?>